﻿using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using CoreApp.Model;
using Dapper;

namespace CoreApp
{
    public class DbHelper
    {
        static IDbConnection connection = new SqlConnection(ConfigurationManager.AppSettings["conneString"]);

        public static List<dynamic> Query(string sql)
        {
            return connection.Query<dynamic>(sql).ToList();
        }


        public static List<T> Query<T>(string sql)
        {
            return connection.Query<T>(sql).ToList();
        }


        /// <summary>
        /// 根据咨询id查手机号码
        /// </summary>
        /// <param name="consulId"></param>
        /// <returns></returns>
        public static ConsulSendMsgMobile QueryMobileNumberByConsul(int consulId)
        {
            StringBuilder sb = new StringBuilder();
            sb.Append("declare @i int");
            sb.Append("declare @j int");
            sb.Append(string.Format("select @i=a.member_id,@j=a.expert_id from dbo.consultation_userinfo as a where id ={0}",consulId));
            sb.Append(" select member_phone,member_type from member_base where id in(@i,@j)");
            var list= connection.Query<ConsulSendMsgData>(sb.ToString()).ToList();
            string memberPhoneNumber = list.Where(x => x.member_type == 1).Select(x => x.member_phone).FirstOrDefault();
            string expertPhoneNumber = list.Where(x => x.member_type == 2).Select(x => x.member_phone).FirstOrDefault();
            //根据咨询id查询用户与专家的电话号码
            return new ConsulSendMsgMobile() { memberPhoneNumber = memberPhoneNumber, expertPhoneNumber = expertPhoneNumber };
        }


        public static member_base GetMemberForId(int id)
        {
            string sql = string.Format("select * from member_base where id={0}",id);
            return connection.Query<member_base>(sql).FirstOrDefault();
        }

        public static consultation_msg_temp GetMsgForOrderId(int orderId)
        {
            //查询该订单的第一次回复的消息的id，然后根据这个id查formid
            string sql = string.Format("select top 1 * from dbo.consultation_msg_temp where order_id={0} order by msg_time desc", orderId);
            return connection.QueryFirstOrDefault<consultation_msg_temp>(sql);
        }




        public static weChatFormIds GetFormIds(int msgId)
        {
            string sql = string.Format("select * from weChatFormIds where union_id={0}",msgId);
            return connection.Query<weChatFormIds>(sql).FirstOrDefault();
        }



        public static void UpdateFormIds(weChatFormIds model)
        {
            string sql = "UPDATE [dbo].[weChatFormIds] SET [formId1] = @formId1,[isUsed1] = @isUsed1" +
                ",[formId2] = @formId2,[isUsed2] = @isUsed2,[formId3] = @formId3 ,[isUsed3] = @isUsed3" +
                ",[union_id] = @union_id,[formId4] = @formId4,[isUsed4] = @isUsed4 where id=@id";
            connection.Execute(sql, model);
        }



        /// <summary>
        /// 结束咨询
        /// </summary>
        /// <param name="OrderId"></param>
        public static void EndConsult(int OrderId,int consulId)
        {
            string sql = string.Format("update store_order set order_status=2 where id={0}", OrderId);
            connection.Execute(sql);
            string sqlCon = string.Format("update consultation_userinfo set consultation_status=2 where id={0}", consulId);
            connection.Execute(sqlCon);

            string sql2 = string.Format("select * from consultation_msg_temp where order_id={0} order by msg_time desc", OrderId );
            List<consultation_msg_temp> tList =connection.Query<consultation_msg_temp>(sql2).ToList();
            string del = "delete from consultation_msg_temp where id={0}";
            StringBuilder insertSql = new StringBuilder();
            insertSql.Append("INSERT INTO [consultation_msg]");
            insertSql.Append(" ([consultation_userinfoid],[msg_from_id],[msg_from_name],[msg_from_headimage],[msg_to_id],[msg_to_name],[msg_to_headimage]");
            insertSql.Append(",[msg_text],[msg_image],[msg_audio],[msg_video],[msg_duration],[msg_time],[msg_status],[order_id],[msg_type]) VALUES");
            insertSql.Append("(@consultation_userinfoid,@msg_from_id,@msg_from_name,@msg_from_headimage,@msg_to_id,@msg_to_name,@msg_to_headimage,@msg_text,@msg_image,@msg_audio,@msg_video,@msg_duration,@msg_time,@msg_status,@order_id,@msg_type)");
            //string insert = "INSERT INTO consultation_msg values({0},{1},{2},{3},{4},{5},{6},{7},{8},{9},{10},{11},{12},{13},{14},{15})";
            foreach (var item in tList)
            {
                consultation_msg temp = new consultation_msg();
                temp.consultation_userinfoid = item.consultation_userinfoid;
                temp.msg_time = item.msg_time;
                temp.msg_text = item.msg_text;
                temp.msg_video = item.msg_video;
                temp.msg_audio = item.msg_audio;
                temp.msg_duration = item.msg_duration;
                temp.msg_type = item.msg_type;
                temp.order_id = item.order_id;
                temp.msg_from_id = item.msg_from_id;
                temp.msg_to_id = item.msg_to_id;
                temp.msg_status = item.msg_status;
                temp.formId = item.formId;
                temp.msg_image = item.msg_image;
                connection.Execute(insertSql.ToString(), temp);
                connection.Execute(string.Format(del, item.id));
            }
        }


        /// <summary>
        /// 提交离线咨询
        /// </summary>
        public static void SubmitOffConsult(int consulId)
        {
            string sql = "update consultation_userinfo set consultation_status=4 where id="+consulId;
            connection.Execute(sql);
        }


    }

    public class ConsulSendMsgMobile
    {

        public string memberPhoneNumber { get; set; }

        public string expertPhoneNumber { get; set; }

    }

    public class ConsulSendMsgData
    {

        public string member_phone { get; set; }

        public int member_type { get; set; }

    }


}
